﻿using System;
using System.Collections.Generic;
using System.Data.Common;
using Entities;
using WebLog;
namespace Data
{
    public sealed class VoterData
    {
        public Voter GetVoterById(int id)
        {
            DataBase bd = new DataBase();
            Voter voter = null;
            DbDataReader readerDataSource = null;
            try
            {
                bd.Connect();
                bd.ProcedureSqlName("Obtener_Votante_Por_Id");
                bd.AssignInt("@Id_votante", id);
                readerDataSource = bd.ExecuteDataReader();
                if (readerDataSource.HasRows)
                {
                    while (readerDataSource.Read())
                    {
                        voter = LoadVoter(readerDataSource);
                    }
                    readerDataSource.Close();
                    readerDataSource.Dispose();

                    bd.ProcedureSqlName("OBTENER_VOTANTE_VOTACION");
                    bd.AssignInt("@Id_votante", id);
                    DbDataReader votaciones = bd.ExecuteDataReader();
                    if (votaciones.HasRows)
                    {
                        while (votaciones.Read())
                        {
                            Voting v = new Voting();
                            v.id = votaciones.GetInt32(0).ToString();
                            v.name = votaciones.GetString(1);
                            v.votingDateStart = votaciones.GetDateTime(2);
                            v.votingDateEnd = votaciones.GetDateTime(3);
                            v.votingStartTime = votaciones.GetString(4);
                            v.votingEndTime = votaciones.GetString(5);
                            v.votingBlocked = votaciones.GetBoolean(6);
                            v.votingActive = votaciones.GetBoolean(7);
                            voter.votingList.Add(v);
                        }
                        votaciones.Close();
                        votaciones.Dispose();
                    }
                }
                else
                {
                    voter = null;
                }
            }
            catch (Exception e)
            {
                Logger logger = Logger.GetInstance;
                logger.WriteLog(e);
                voter = null;
            }

            finally
            {

                bd.Disconnect();
                bd.ReleaseResources();
            }
            return voter;
        }

        public bool InsertVoter(Voter i)
        {
            //ya cache..
            // tengo que consultat en la tabla grupo votacion
            //si hay registros inserto datos en votante votacion
            string body = i.rut.Substring(0, i.rut.Length - 1);
            string digit = i.rut.Substring(i.rut.Length - 1, 1);
            DataBase bd = new DataBase();
            bool response = false;
            try
            {
                bd.Connect();
                bd.BeginTransaction();
                bd.ProcedureSqlName("INSERTAR_VOTANTE");
                bd.AssignString("@Rut_votante", body);
                bd.AssignString("@Digito_Verificador_votante", digit);
                bd.AssignString("@Nombre_votante", i.name);
                bd.AssignString("@Segundo_Nombre_votante", i.middleName);
                bd.AssignString("@Primer_Apellido_votante", i.lastName);
                bd.AssignString("@Segundo_Apellido_votante", i.secondLastName);
                bd.AssignDateTime("@Fecha_Nacimiento_votante", i.birthDate);
                bd.AssignString("@Email_votante", i.email);
                bd.AssignString("@Direccion_votante", i.address);
                bd.AssignString("@Villa_votante", i.smallTown);
                bd.AssignInt("@id_region_votante", Convert.ToInt32(i.regionId));
                bd.AssignString("@Telefono_votante", i.telephone);
                bd.AssignString("@Celular_votante", i.cellPhone);
                bd.AssignDateTime("@fecha_Inscripcion_votante", i.registrationDate);
                bd.AssignString("@contrasena_votante", i.password);
                bd.AssignBool("@Bloqueado_votante", i.blocked);
                bd.AssignBool("@activo_votante", i.isActive);

                bd.AssignInt("@sexo_votante", Convert.ToInt32(i.sex));
                bd.AssignInt("@estado_civil_votante", Convert.ToInt32(i.civilStatus));
                bd.AssignInt("@ingreso_id_usuario", Convert.ToInt32(i.UserId));
                bd.AssignInt("@id_grupo", Convert.ToInt32(i.group.id));
                bd.AssignInt("@id_comuna", Convert.ToInt32(i.communeId));

                int idVotante = bd.ExecuteScalar();
                if (idVotante > 0)
                {
                    
                    bd.ProcedureSqlName("OBTENER_VOTACION_POR_GRUPO");
                    bd.AssignString("@ID_GRUPO", i.group.id);
                    var result = bd.ExecuteDataReader();

                    List<int> votingId = new List<int>();
                    foreach (DbDataRecord row in result)
                    {
                        votingId.Add(row.GetInt32(0));
                    }
                    
                    result.Close();
                    result.Dispose();

                    

                    bool insertRelation = false;
                    foreach (int vi in votingId)
                    {
                        bd.ProcedureSqlName("INSERTAR_VOTANTE_VOTACION");
                        bd.AssignInt("@ID_VOTANTE", idVotante);
                        bd.AssignInt("@ID_VOTACION", vi);
                        var relation = bd.ExecuteScalar();


                        if (relation > 0)
                        {
                            insertRelation = true;
                        }
                        else
                        {
                            insertRelation = false;
                            break;
                        }
                    }

                    if (votingId.Count == 0)
                    {
                        insertRelation = true;
                    }


                    if (insertRelation)
                    {
                        bd.CommitTransaction();
                        response = true;
                    }
                    else
                    {
                        bd.RollbackTransaction();
                        response = false;
                    }
                }

                else
                {
                    bd.RollbackTransaction();
                    response = false;
                }
            }
            catch (Exception e)
            {
                Logger logger = Logger.GetInstance;
                logger.WriteLog(e);
                bd.RollbackTransaction();
                response = false;
            }
            finally
            {
                bd.ReleaseResources();
                bd.Disconnect();
            }
            return response;
        }

        public bool InsertVoterRegister(Voter i)
        {
            DataBase bd = new DataBase();
            bool response = false;
            try
            {
                bd.Connect();
                bd.ProcedureSqlName("INSERTAR_REGISTRO_VOTANTE");
                bd.AssignDateTime("@Ultimo_Ingreso", DateTime.Now);
                bd.AssignString("@Ip", i.register.ipLastLogin);
                bd.AssignString("@Ciudad", i.register.city);
                bd.AssignString("@Region", i.register.region);
                bd.AssignString("@Pais", i.register.country);
                bd.AssignString("@Navegador", i.register.browser);
                bd.AssignInt("@Id_Votante", Convert.ToInt32(i.id));


                int query = bd.ExecuteNonQuery();
                if (query > 0)
                {
                    response = true;
                }

                else
                {
                    response = false;
                }
            }
            catch (Exception e)
            {
                Logger logger = Logger.GetInstance;
                logger.WriteLog(e);
                response = false;
            }
            finally
            {
                bd.ReleaseResources();
                bd.Disconnect();
            }
            return response;
        }

        public bool SearchVoterById(Voter i)
        {

            string body = i.rut.Substring(0, i.rut.Length - 1);
            string digit = i.rut.Substring(i.rut.Length - 1, 1);
            DataBase bd = new DataBase();
            bool response = false;

            try
            {
                bd.Connect();
                bd.ProcedureSqlName("BUSCAR_POR_RUT_VOTANTE");
                bd.AssignString("@Rut_votante", body);
                bd.AssignString("@Digito_Verificador_votante", digit);

                int resultado = bd.ExecuteScalar();
                if (resultado > 0)
                {
                    response = true;
                }
            }

            catch (Exception e)
            {
                Logger logger = Logger.GetInstance;
                logger.WriteLog(e);
                response = false;
            }
            finally
            {
                bd.ReleaseResources();
                bd.Disconnect();
            }
            return response;

        }

        public Voter GetVoter(string rutBody, string digit, string password)
        {
            DataBase bd = new DataBase();
            Voter voter = null;
            DbDataReader readerDataSource = null;
            try
            {
                bd.Connect();
                bd.ProcedureSqlName("OBTENER_VOTANTE");
                bd.AssignString("@Rut_votante", rutBody);
                bd.AssignString("@Digito_Verificador_votante", digit);
                bd.AssignString("@contrasena_votante", password);
                readerDataSource = bd.ExecuteDataReader();
                if (readerDataSource.HasRows)
                {
                    while (readerDataSource.Read())
                    {
                        voter = LoadVoter(readerDataSource);
                    }
                    readerDataSource.Dispose();
                    readerDataSource.Close();
                }
                else
                {
                    voter = null;
                }
            }

            catch (Exception e)
            {
                Logger logger = Logger.GetInstance;
                logger.WriteLog(e);
                voter = null;
            }
            finally
            {

                bd.ReleaseResources();
                bd.Disconnect();
            }
            return voter;
        }

        public bool FindVoterByEmail(string email)
        {
            DataBase bd = new DataBase();
            bool response = false;
            try
            {
                bd.Connect();
                bd.ProcedureSqlName("BUSCAR_POR_EMAIL_VOTANTE");
                bd.AssignString("@Email_votante", email);
                int resultado = bd.ExecuteScalar();
                if (resultado > 0)
                {
                    response = true;
                }

            }
            catch (Exception e)
            {
                Logger logger = Logger.GetInstance;
                logger.WriteLog(e);
                response = false;
            }
            finally
            {
                bd.ReleaseResources();
                bd.Disconnect();
            }
            return response;
        }

        public List<Register> GetListVoterRegister(int id)
        {
            DataBase bd = new DataBase();
            List<Register> list = new List<Register>();
            try
            {
                bd.Connect();
                bd.ProcedureSqlName("OBTENER_REGISTROS_VOTANTE");
                bd.AssignInt("@Id_Votante", Convert.ToInt32(id));
                DbDataReader readerDataSource = bd.ExecuteDataReader();
                if (readerDataSource.HasRows)
                {
                    while (readerDataSource.Read())
                    {
                        Register r = new Register();
                        r.id = (readerDataSource.IsDBNull(0)) ? "" : readerDataSource.GetInt32(0).ToString();

                        if (!readerDataSource.IsDBNull(1))
                        {
                            r.dateLastLogin = readerDataSource.GetDateTime(1);
                            r.dateLastLoginView = string.Format("{0:d MMMM yyyy}", r.dateLastLogin);
                            r.hourLastLogin = string.Format("{0:hh:mm}", r.dateLastLogin);
                        }
                        else
                        {
                            r.dateLastLoginView = "";
                        }

                        r.ipLastLogin = (readerDataSource.IsDBNull(2)) ? "" : readerDataSource.GetString(2);
                        r.city = (readerDataSource.IsDBNull(3)) ? "" : readerDataSource.GetString(3);
                        r.region = (readerDataSource.IsDBNull(4)) ? "" : readerDataSource.GetString(4);
                        r.country = (readerDataSource.IsDBNull(5)) ? "" : readerDataSource.GetString(5);
                        r.browser = (readerDataSource.IsDBNull(6)) ? "" : readerDataSource.GetString(6);
                        r.idVoter = (readerDataSource.IsDBNull(7)) ? "" : readerDataSource.GetInt32(7).ToString();
                        list.Add(r);
                    }
                    readerDataSource.Dispose();
                    readerDataSource.Close();
                }
                else
                {
                    list = null;
                }

            }

            catch (Exception e)
            {
                Logger logger = Logger.GetInstance;
                logger.WriteLog(e);
                list = null;
            }
            finally
            {
                bd.Disconnect();
                bd.ReleaseResources();
            }
            return list;
        }

        public List<Voter> GetListVoters()
        {
            DataBase bd = new DataBase();
            List<Voter> list = new List<Voter>();
            try
            {
                bd.Connect();
                bd.ProcedureSqlName("OBTENER_VOTANTES");

                DbDataReader readerDataSource = bd.ExecuteDataReader();
                if (readerDataSource.HasRows)
                {
                    while (readerDataSource.Read())
                    {
                        list.Add(LoadVoter(readerDataSource));
                    }
                    readerDataSource.Dispose();
                    readerDataSource.Close();
                }
                else
                {
                    list = null;
                }

            }

            catch (Exception e)
            {
                Logger logger = Logger.GetInstance;
                logger.WriteLog(e);
                list = null;
            }
            finally
            {
                bd.Disconnect();
                bd.ReleaseResources();
            }
            return list;
        }

        public List<Voter> SearchVoterByRut(string rut)
        {
            int body = Convert.ToInt32(rut.Substring(0, rut.Length - 1));
            string digit = rut.Substring(rut.Length - 1, 1);
            DataBase bd = new DataBase();
            List<Voter> list = new List<Voter>();
            try
            {
                bd.Connect();
                bd.ProcedureSqlName("BUSCAR_POR_RUT_VOTANTE");
                bd.AssignInt("@Rut_votante", body);
                bd.AssignString("@Digito_Verificador_votante", digit);
                DbDataReader readerDataSource = bd.ExecuteDataReader();
                if (readerDataSource.HasRows)
                {
                    while (readerDataSource.Read())
                    {
                        list.Add(LoadVoter(readerDataSource));
                    }
                    readerDataSource.Dispose();
                    readerDataSource.Close();
                }
                else
                {
                    list = null;
                }

            }

            catch (Exception e)
            {
                Logger logger = Logger.GetInstance;
                logger.WriteLog(e);
                list = null;
            }
            finally
            {
                bd.ReleaseResources();
                bd.Disconnect();
            }
            return list;
        }

        public List<Voter> SearchVoterByName(string name)
        {

            DataBase bd = new DataBase();
            List<Voter> list = new List<Voter>();
            try
            {
                bd.Connect();
                bd.ProcedureSqlName("BUSCAR_POR_NOMBRE_VOTANTE");
                bd.AssignString("@Nombre_votante", name);
                DbDataReader readerDataSource = bd.ExecuteDataReader();
                if (readerDataSource.HasRows)
                {
                    while (readerDataSource.Read())
                    {
                        list.Add(LoadVoter(readerDataSource));
                    }
                    readerDataSource.Dispose();
                    readerDataSource.Close();
                }
                else
                {
                    list = null;
                }

            }

            catch (Exception e)
            {
                Logger logger = Logger.GetInstance;
                logger.WriteLog(e);
                list = null;
            }
            finally
            {
                bd.ReleaseResources();
                bd.Disconnect();
            }
            return list;
        }

        public List<Voter> SearchVoterByLastName(string lastName)
        {
            DataBase bd = new DataBase();
            List<Voter> list = new List<Voter>();
            try
            {
                bd.Connect();
                bd.ProcedureSqlName("BUSCAR_POR_APELLIDO_VOTANTE");
                bd.AssignString("@Primer_Apellido_votante", lastName);
                DbDataReader readerDataSource = bd.ExecuteDataReader();
                if (readerDataSource.HasRows)
                {
                    while (readerDataSource.Read())
                    {
                        list.Add(LoadVoter(readerDataSource));
                    }
                    readerDataSource.Dispose();
                    readerDataSource.Close();
                }
                else
                {
                    list = null;
                }
            }
            catch (Exception e)
            {
                Logger logger = Logger.GetInstance;
                logger.WriteLog(e);
                list = null;
            }
            finally
            {
                bd.ReleaseResources();
                bd.Disconnect();
            }
            return list;
        }

        public List<Voter> SearchVoterByEmail(string email)
        {
            DataBase bd = new DataBase();
            List<Voter> list = new List<Voter>();
            try
            {
                bd.Connect();
                bd.ProcedureSqlName("BUSCAR_POR_EMAIL_VOTANTE");
                bd.AssignString("@Email_votante", email);
                DbDataReader readerDataSource = bd.ExecuteDataReader();
                if (readerDataSource.HasRows)
                {
                    while (readerDataSource.Read())
                    {
                        list.Add(LoadVoter(readerDataSource));
                    }
                    readerDataSource.Dispose();
                    readerDataSource.Close();
                }
                else
                {
                    list = null;
                }
            }
            catch (Exception e)
            {
                Logger logger = Logger.GetInstance;
                logger.WriteLog(e);
                list = null;
            }
            finally
            {
                bd.ReleaseResources();
                bd.Disconnect();
            }
            return list;
        }

        public List<Voter> SearchVoterByTelephone(string telephone)
        {
            DataBase bd = new DataBase();
            List<Voter> list = new List<Voter>();
            try
            {
                bd.Connect();
                bd.ProcedureSqlName("BUSCAR_POR_TELEFONO_VOTANTE");
                bd.AssignString("@Telefono_votante", telephone);
                DbDataReader readerDataSource = bd.ExecuteDataReader();
                if (readerDataSource.HasRows)
                {
                    while (readerDataSource.Read())
                    {
                        list.Add(LoadVoter(readerDataSource));
                    }
                    readerDataSource.Dispose();
                    readerDataSource.Close();
                }
                else
                {
                    list = null;
                }
            }
            catch (Exception e)
            {
                Logger logger = Logger.GetInstance;
                logger.WriteLog(e);
                list = null;
            }
            finally
            {
                bd.ReleaseResources();
                bd.Disconnect();
            }
            return list;
        }

        public List<Voter> SearchVoterByCellphone(string cellphone)
        {
            DataBase bd = new DataBase();
            List<Voter> list = new List<Voter>();
            try
            {
                bd.Connect();
                bd.ProcedureSqlName("BUSCAR_POR_CELULAR_VOTANTE");
                bd.AssignString("@Celular_votante", cellphone);
                DbDataReader readerDataSource = bd.ExecuteDataReader();
                if (readerDataSource.HasRows)
                {
                    while (readerDataSource.Read())
                    {
                        list.Add(LoadVoter(readerDataSource));
                    }
                    readerDataSource.Dispose();
                    readerDataSource.Close();
                }
                else
                {
                    list = null;
                }
            }
            catch (Exception e)
            {
                Logger logger = Logger.GetInstance;
                logger.WriteLog(e);
                list = null;
            }
            finally
            {
                bd.ReleaseResources();
                bd.Disconnect();
            }
            return list;
        }

        public List<Voter> SearchVoterByDate(DateTime date)
        {
            DataBase bd = new DataBase();
            List<Voter> list = new List<Voter>();
            try
            {
                bd.Connect();
                bd.ProcedureSqlName("BUSCAR_POR_FECHA_INSCRIPCION_VOTANTE");
                bd.AssignDateTime("@fecha_Inscripcion_votante", date);
                DbDataReader readerDataSource = bd.ExecuteDataReader();
                if (readerDataSource.HasRows)
                {
                    while (readerDataSource.Read())
                    {
                        list.Add(LoadVoter(readerDataSource));
                    }
                    readerDataSource.Dispose();
                    readerDataSource.Close();
                }
                else
                {
                    list = null;
                }
            }
            catch (Exception e)
            {
                Logger logger = Logger.GetInstance;
                logger.WriteLog(e);
                list = null;
            }
            finally
            {
                bd.ReleaseResources();
                bd.Disconnect();
            }
            return list;
        }

        public List<Voter> SearchVoterByBlocked(bool blocked)
        {
            DataBase bd = new DataBase();
            List<Voter> list = new List<Voter>();
            try
            {
                bd.Connect();
                bd.ProcedureSqlName("BUSCAR_POR_BLOQUEADO_VOTANTE");
                bd.AssignBool("@Bloqueado_votante", blocked);
                DbDataReader readerDataSource = bd.ExecuteDataReader();
                if (readerDataSource.HasRows)
                {
                    while (readerDataSource.Read())
                    {
                        list.Add(LoadVoter(readerDataSource));
                    }
                    readerDataSource.Dispose();
                    readerDataSource.Close();
                }
                else
                {
                    list = null;
                }
            }
            catch (Exception e)
            {
                Logger logger = Logger.GetInstance;
                logger.WriteLog(e);
                list = null;
            }
            finally
            {
                bd.ReleaseResources();
                bd.Disconnect();
            }
            return list;
        }

        public List<string> AutocompleteByName(string name)
        {
            DataBase bd = new DataBase();
            List<string> list = new List<string>();
            DbDataReader readerDataSource = null;
            try
            {
                bd.Connect();
                bd.ProcedureSqlName("AUTOCOMPLETAR_NOMBRE_VOTANTE");
                bd.AssignString("@Nombre_votante", name);
                readerDataSource = bd.ExecuteDataReader();

                if (readerDataSource.HasRows)
                {
                    while (readerDataSource.Read())
                    {
                        list.Add(readerDataSource.GetString(0));
                    }
                    readerDataSource.Dispose();
                    readerDataSource.Close();
                }
                else
                {
                    list = null;
                }
            }
            catch (Exception e)
            {
                Logger logger = Logger.GetInstance;
                logger.WriteLog(e);
                list = null;
            }
            finally
            {

                bd.ReleaseResources();
                bd.Disconnect();
            }
            return list;
        }

        public List<string> AutocompleteByLastName(string lastName)
        {
            DataBase bd = new DataBase();
            List<string> list = new List<string>();
            DbDataReader readerDataSource = null;
            try
            {
                bd.Connect();
                bd.ProcedureSqlName("AUTOCOMPLETAR_APELLIDO_VOTANTE");
                bd.AssignString("@Primer_Apellido_votante", lastName);
                readerDataSource = bd.ExecuteDataReader();

                if (readerDataSource.HasRows)
                {
                    while (readerDataSource.Read())
                    {
                        list.Add(readerDataSource.GetString(0));
                    }
                    readerDataSource.Dispose();
                    readerDataSource.Close();
                }
                else
                {
                    list = null;
                }
            }
            catch (Exception e)
            {
                Logger logger = Logger.GetInstance;
                logger.WriteLog(e);
                list = null;
            }
            finally
            {

                bd.ReleaseResources();
                bd.Disconnect();
            }
            return list;
        }

        public List<string> AutocompleteByRut(string rut)
        {
            DataBase bd = new DataBase();
            List<string> list = new List<string>();
            DbDataReader readerDataSource = null;
            try
            {
                bd.Connect();
                bd.ProcedureSqlName("AUTOCOMPLETAR_RUT_VOTANTE");
                bd.AssignString("@Rut_votante", rut);
                readerDataSource = bd.ExecuteDataReader();

                if (readerDataSource.HasRows)
                {
                    while (readerDataSource.Read())
                    {
                        list.Add(readerDataSource.GetString(0) + readerDataSource.GetString(1));
                    }
                    readerDataSource.Dispose();
                    readerDataSource.Close();
                }
                else
                {
                    list = null;
                }
            }
            catch (Exception e)
            {
                Logger logger = Logger.GetInstance;
                logger.WriteLog(e);
                list = null;
            }
            finally
            {
                bd.ReleaseResources();
                bd.Disconnect();

            }
            return list;
        }

        public List<string> AutocompleteByEmail(string email)
        {
            DataBase bd = new DataBase();
            List<string> list = new List<string>();
            DbDataReader readerDataSource = null;
            try
            {
                bd.Connect();
                bd.ProcedureSqlName("AUTOCOMPLETAR_MAIL_VOTANTE");
                bd.AssignString("@Email_votante", email);
                readerDataSource = bd.ExecuteDataReader();

                if (readerDataSource.HasRows)
                {
                    while (readerDataSource.Read())
                    {
                        list.Add(readerDataSource.GetString(0));
                    }
                    readerDataSource.Dispose();
                    readerDataSource.Close();
                }
                else
                {
                    list = null;
                }
            }
            catch (Exception e)
            {
                Logger logger = Logger.GetInstance;
                logger.WriteLog(e);
                list = null;
            }
            finally
            {

                bd.ReleaseResources();
                bd.Disconnect();
            }
            return list;
        }

        public bool DeleteVoter(int id)
        {
            DataBase bd = new DataBase();
            bool response = false;
            try
            {
                bd.Connect();
                bd.BeginTransaction();
                bd.ProcedureSqlName("ELIMINAR_VOTANTE_VOTACION");
                bd.AssignInt("@Id_votante", id);
                int query = bd.ExecuteNonQuery();

                bd.ProcedureSqlName("ELIMINAR_VOTANTE");
                bd.AssignInt("@Id_votante", id);
                int elimina = bd.ExecuteNonQuery();

                if (elimina > 0)
                {
                    bd.CommitTransaction();
                    response = true;
                }
                else
                {
                    bd.RollbackTransaction();
                    response = false;
                }

            }

            catch (Exception e)
            {
                Logger logger = Logger.GetInstance;
                logger.WriteLog(e);
                bd.RollbackTransaction();
                response = false;
            }
            finally
            {
                bd.ReleaseResources();
                bd.Disconnect();
            }
            return response;
        }

        public bool ChangeVoterPassword(Voter i)
        {
            DataBase bd = new DataBase();
            bool response = false;
            try
            {
                bd.Connect();
                bd.ProcedureSqlName("CAMBIAR_PASSWORD_VOTANTE");
                bd.AssignString("@contrasena_votante", i.password);
                bd.AssignInt("@Id_votante", Convert.ToInt32(i.id));

                int registroActualizado = bd.ExecuteNonQuery();
                if (registroActualizado > 0)
                {

                    response = true;
                }
                else
                {
                    response = false;
                }
            }

            catch (Exception e)
            {
                Logger logger = Logger.GetInstance;
                logger.WriteLog(e);
                response = false;
            }
            finally
            {
                bd.ReleaseResources();
                bd.Disconnect();
            }
            return response;

        }

        public bool UpgradeVoter(Voter i)
        {
            string body = i.rut.Substring(0, i.rut.Length - 1);
            string digit = i.rut.Substring(i.rut.Length - 1, 1);
            DataBase bd = new DataBase();
            bool response = false;
            try
            {
                bd.Connect();
                bd.BeginTransaction();
                bd.ProcedureSqlName("ACTUALIZAR_VOTANTE");
                bd.AssignInt("@Id_votante", Convert.ToInt32(i.id));
                bd.AssignString("@Rut_votante", body);
                bd.AssignString("@Digito_Verificador_votante", digit);
                bd.AssignString("@Nombre_votante", i.name);
                bd.AssignString("@Segundo_Nombre_votante", i.middleName);
                bd.AssignString("@Primer_Apellido_votante", i.lastName);
                bd.AssignString("@Segundo_Apellido_votante", i.secondLastName);
                bd.AssignDateTime("@Fecha_Nacimiento_votante", i.birthDate);
                bd.AssignString("@Email_votante", i.email);
                bd.AssignString("@Direccion_votante", i.address);
                bd.AssignString("@Villa_votante", i.smallTown);
                bd.AssignInt("@id_region_votante", Convert.ToInt32(i.regionId));
                bd.AssignString("@Telefono_votante", i.telephone);
                bd.AssignString("@Celular_votante", i.cellPhone);
                bd.AssignDateTime("@fecha_Inscripcion_votante", i.registrationDate);
                bd.AssignString("@remoteIP_votante", i.remoteIP);
                bd.AssignString("@remotoHost_votante", i.remotoHost);
                bd.AssignBool("@Bloqueado_votante", i.blocked);

                int registroActualizado = bd.ExecuteNonQuery();
                if (registroActualizado > 0)
                {
                    if (i.votingList.Count == 0)
                    {
                        bd.CommitTransaction();
                        response = true;
                    }
                    else
                    {
                        int cont = 0;
                        foreach (Voting v in i.votingList)
                        {
                            bd.ProcedureSqlName("INSERTAR_VOTANTE_VOTACION");
                            bd.AssignInt("@ID_VOTANTE", Convert.ToInt32(i.id));
                            bd.AssignInt("@ID_VOTACION", Convert.ToInt32(v.id));
                            int idRelacion = bd.ExecuteScalar();
                            if (idRelacion > 0)
                            {
                                cont++;
                            }
                        }

                        if (cont == i.votingList.Count)
                        {
                            bd.CommitTransaction();
                            response = true;
                        }
                        else
                        {
                            bd.RollbackTransaction();
                            response = false;
                        }
                    }
                }

                else
                {
                    bd.RollbackTransaction();
                    response = false;
                }
            }
            catch (Exception e)
            {
                Logger logger = Logger.GetInstance;
                logger.WriteLog(e);
                bd.RollbackTransaction();
                response = false;
            }
            finally
            {
                bd.ReleaseResources();
                bd.Disconnect();
            }
            return response;
        }

        public bool UpgradeVoterPicture(Voter i)
        {
            DataBase bd = new DataBase();
            bool response = false;
            try
            {
                bd.Connect();
                bd.ProcedureSqlName("ACTUALIZAR_FOTOGRAFIA_VOTANTE");
                bd.AssignInt("@Id_votante", Convert.ToInt32(i.id));
                bd.AssignString("@IMAGEN_VOTANTE", i.picturePath);
                int elimina = bd.ExecuteNonQuery();

                if (elimina > 0)
                {
                    response = true;
                }
                else
                {
                    response = false;
                }

            }

            catch (Exception e)
            {
                Logger logger = Logger.GetInstance;
                logger.WriteLog(e);
                response = false;
            }
            finally
            {
                bd.ReleaseResources();
                bd.Disconnect();
            }
            return response;
        }

        public bool UpdateVoterPassword(Voter i, string newPassword)
        {
            DataBase bd = new DataBase();
            bool response = false;
            try
            {
                bd.Connect();
                bd.ProcedureSqlName("ACTUALIZAR_PASSWORD_VOTANTE");
                bd.AssignInt("@ID_VOTANTE", Convert.ToInt32(i.id));
                bd.AssignString("@CONTRASENA_VOTANTE", newPassword);
                int delete = bd.ExecuteNonQuery();

                if (delete > 0)
                {
                    response = true;
                }
                else
                {
                    response = false;
                }

            }

            catch (Exception e)
            {
                Logger logger = Logger.GetInstance;
                logger.WriteLog(e);
                response = false;
            }
            finally
            {
                bd.ReleaseResources();
                bd.Disconnect();
            }
            return response;
        }

        public bool CheckVoterOldPassword(Voter i, string oldPassword)
        {
            DataBase bd = new DataBase();
            bool response = false;
            try
            {
                bd.Connect();
                bd.ProcedureSqlName("VERIFICAR_PASSWORD_ANTIGUA_VOTANTE");
                bd.AssignInt("@ID_VOTANTE", Convert.ToInt32(i.id));
                bd.AssignString("@CONTRASENA_VOTANTE", oldPassword);
                int query = bd.ExecuteScalar();

                if (query > 0)
                {
                    response = true;
                }
                else
                {
                    response = false;
                }

            }

            catch (Exception e)
            {
                Logger logger = Logger.GetInstance;
                logger.WriteLog(e);
                response = false;
            }
            finally
            {
                bd.ReleaseResources();
                bd.Disconnect();
            }
            return response;
        }

        public List<CivilStatus> GetListCivilStatus()
        {
            DataBase bd = new DataBase();
            List<CivilStatus> list = new List<CivilStatus>();
            DbDataReader readerDataSource = null;
            try
            {
                bd.Connect();
                bd.ProcedureSqlName("OBTENER_ESTADO_CIVIL");

                readerDataSource = bd.ExecuteDataReader();

                if (readerDataSource.HasRows)
                {
                    while (readerDataSource.Read())
                    {
                        CivilStatus cs = new CivilStatus();
                        cs.id = readerDataSource.GetInt32(0).ToString();
                        cs.status = readerDataSource.GetString(1);
                        list.Add(cs);
                    }
                    readerDataSource.Dispose();
                    readerDataSource.Close();
                }
                else
                {
                    list = null;
                }
            }
            catch (Exception e)
            {
                Logger logger = Logger.GetInstance;
                logger.WriteLog(e);
                list = null;
            }
            finally
            {

                bd.ReleaseResources();
                bd.Disconnect();
            }
            return list;
        }

        public List<Gender> GetListSex()
        {
            DataBase bd = new DataBase();
            List<Gender> list = new List<Gender>();
            DbDataReader readerDataSource = null;
            try
            {
                bd.Connect();
                bd.ProcedureSqlName("OBTENER_SEXOS");

                readerDataSource = bd.ExecuteDataReader();

                if (readerDataSource.HasRows)
                {
                    while (readerDataSource.Read())
                    {
                        Gender cs = new Gender();
                        cs.id = readerDataSource.GetInt32(0).ToString();
                        cs.sex = readerDataSource.GetString(1);
                        list.Add(cs);
                    }
                    readerDataSource.Dispose();
                    readerDataSource.Close();
                }
                else
                {
                    list = null;
                }
            }
            catch (Exception e)
            {
                Logger logger = Logger.GetInstance;
                logger.WriteLog(e);
                list = null;
            }
            finally
            {

                bd.ReleaseResources();
                bd.Disconnect();
            }
            return list;
        }

        public List<Group> GetlistOfGroup()
        {
            DataBase bd = new DataBase();
            List<Group> list = new List<Group>();
            DbDataReader readerDataSource = null;
            try
            {
                bd.Connect();
                bd.ProcedureSqlName("OBTENER_GRUPOS");

                readerDataSource = bd.ExecuteDataReader();

                if (readerDataSource.HasRows)
                {
                    while (readerDataSource.Read())
                    {
                        Group cs = new Group();
                        cs.id = readerDataSource.GetInt32(0).ToString();
                        cs.name = readerDataSource.GetString(1);
                        list.Add(cs);
                    }
                    readerDataSource.Dispose();
                    readerDataSource.Close();
                }
                else
                {
                    list = null;
                }
            }
            catch (Exception e)
            {
                Logger logger = Logger.GetInstance;
                logger.WriteLog(e);
                list = null;
            }
            finally
            {

                bd.ReleaseResources();
                bd.Disconnect();
            }
            return list;
        }

        public Group GetGroupById(string id)
        {
            DataBase bd = new DataBase();
            Group group = new Group();
            DbDataReader readerDataSource = null;
            try
            {
                bd.Connect();
                bd.ProcedureSqlName("OBTENER_GRUPOS_POR_ID");
                bd.AssignInt("@id", Convert.ToInt32(id));
                readerDataSource = bd.ExecuteDataReader();

                if (readerDataSource.HasRows)
                {
                    while (readerDataSource.Read())
                    {

                        group.id = readerDataSource.GetInt32(0).ToString();
                        group.name = readerDataSource.GetString(1);

                    }
                    readerDataSource.Dispose();
                    readerDataSource.Close();
                }
                else
                {
                    group = null;
                }
            }
            catch (Exception e)
            {
                Logger logger = Logger.GetInstance;
                logger.WriteLog(e);
                group = null;
            }
            finally
            {

                bd.ReleaseResources();
                bd.Disconnect();
            }
            return group;
        }

        private Voter LoadVoter(DbDataReader readerDataSource)
        {
            Voter c = new Voter();
            c.id = readerDataSource.GetInt32(0).ToString();
            c.rut = readerDataSource.GetString(1) + readerDataSource.GetString(2);
            c.name = readerDataSource.GetString(3);
            c.middleName = readerDataSource.GetString(4);
            c.lastName = readerDataSource.GetString(5);
            c.secondLastName = readerDataSource.GetString(6);
            c.birthDate = readerDataSource.GetDateTime(7);
            c.email = readerDataSource.GetString(8);
            c.address = readerDataSource.GetString(9);
            c.smallTown = readerDataSource.GetString(10);
            c.regionId = readerDataSource.GetInt32(11).ToString();
            c.telephone = readerDataSource.GetString(12);
            c.cellPhone = readerDataSource.GetString(13);
            c.registrationDate = readerDataSource.GetDateTime(14);
            c.password = readerDataSource.GetString(15);
            c.voucherVotingId = (readerDataSource.IsDBNull(16)) ? "" : readerDataSource.GetString(16);
            c.blocked = readerDataSource.GetBoolean(17);
            c.picturePath = (readerDataSource.IsDBNull(18)) ? "" : readerDataSource.GetString(18); ;
            c.dateView = Convert.ToDateTime(c.registrationDate).ToShortDateString();
            if (c.blocked == true)
            {
                c.blockedView = "Si";
            }
            else
            {
                c.blockedView = "No";
            }
            c.isActive = readerDataSource.GetBoolean(19);
            c.sex = readerDataSource.GetInt32(20).ToString();
            c.civilStatus = readerDataSource.GetInt32(21).ToString();
            c.UserId = readerDataSource.GetInt32(22).ToString();
            c.group.id = readerDataSource.GetInt32(23).ToString();
            c.communeId = readerDataSource.GetInt32(24).ToString();
            return c;
        }



    }
}
